import pandas as pd
import numpy as np
import datetime
xlsx_name = 'D:/repos/sicost/二炼钢KR_data.xlsx'
df1 = pd.read_excel(xlsx_name)
df1.columns = df1.columns.str.upper()
#出钢记号
st_no = df1['ST_NO'].values[0]
print(st_no[:2])
#计划钢种目标S
aim_st_s = df1['AIM_ST_S'].values[0]
# pret_s_aim = df1['PRET_S_AIM'].values[0]
#KR后要求
if st_no == 'IH2554A2':
    kr_s_aim = 2.4
elif st_no[:2] == 'IH' and st_no != 'IH2554A2':
    kr_s_aim = 1.4
elif aim_st_s <= 15:
    kr_s_aim = 1.4
elif aim_st_s <= 20 and aim_st_s > 15:
    kr_s_aim = 2.4
elif aim_st_s <= 30 and aim_st_s > 20:
    kr_s_aim = 5.4
elif aim_st_s <= 50 and aim_st_s > 30:
    kr_s_aim = 1.4
elif aim_st_s <= 100 and aim_st_s > 50:
    kr_s_aim = 2.4
elif aim_st_s < 150 and aim_st_s > 100:
    kr_s_aim = 3.4
elif aim_st_s < 180 and aim_st_s >= 150:
    kr_s_aim = 5.4
elif aim_st_s <= 250 and aim_st_s >= 180:
    kr_s_aim = 5.4
else:
    kr_s_aim = 5.4
pret_s_aim = int(kr_s_aim * 10)
xlsx_name = 'D:/repos/sicost/二炼钢脱硫模型单耗.xlsx'
df2 = pd.read_excel(xlsx_name)
df2.columns = df2.columns.str.upper()
df2['AVG'] = 0.33 * df2['IW'] + 0.17 * (df2['AIM_14'] + df2['AIM_24']) + 0.17 * (df2['AIM_34'] + df2['AIM_54'])
row1 = df2[df2['RECV_S_MAX'] == 300]
value1 = row1['AVG'].values[0] if not row1.empty else 0
row2 = df2[df2['RECV_S_MAX'] == 500]
value2 = row2['AVG'].values[0] if not row2.empty else 0
dh_unit_1 = (value2 - value1) / 20
#受铁后S
# recv_s = df1['RECV_S'].values[0]
column_name_list = df2.columns.tolist()
print(column_name_list)
column_name_tmp = 'AIM_' + str(int(pret_s_aim))
df2_tmp = df2[['RECV_S_MAX', column_name_tmp]]
if st_no[:2] == 'IW':
    df2_tmp = df2[['RECV_S_MAX', 'IW']]
    column_name_tmp = 'IW'
else:
    df2_tmp = df2[['RECV_S_MAX', column_name_tmp]]
#单耗,CAO复合单耗
df2_tmp.rename(columns={column_name_tmp: 'DH'}, inplace=True)
df2_tmp['RECV_S_MAX_NEXT'] = df2_tmp['RECV_S_MAX'].shift(-1)
df2_tmp['DH_NEXT'] = df2_tmp['DH'].shift(-1)
df2_tmp.RECV_S_MAX_NEXT.fillna(99999, inplace=True)
df2_tmp.DH_NEXT.fillna(99999, inplace=True)
xlsx_name = 'D:/repos/sicost/二炼钢脱硫模型时间.xlsx'
df3 = pd.read_excel(xlsx_name)
df3.columns = df3.columns.str.upper()
row3 = df3[df3['RECV_S_MAX'] == 300]
value3 = row3['WHISK_TIME'].values[0] if not row3.empty else 0
row4 = df3[df3['RECV_S_MAX'] == 500]
value4 = row4['WHISK_TIME'].values[0] if not row4.empty else 0
whisk_time_unit_1 = (value4 - value3) / 20
df3['RECV_S_MAX_NEXT'] = df3['RECV_S_MAX'].shift(-1)
df3['WHISK_TIME_NEXT'] = df3['WHISK_TIME'].shift(-1)
df3.RECV_S_MAX_NEXT.fillna(99999, inplace=True)
df3.WHISK_TIME_NEXT.fillna(99999, inplace=True)
def cal_dh_whisk_time(p_recv_s):
    recv_s = p_recv_s
    for index, row in df2_tmp.iterrows():
        recv_s_max_tmp = row['RECV_S_MAX']
        dh_max_tmp = row['DH']
        recv_s_max_next_tmp = row['RECV_S_MAX_NEXT']
        dh_max_next_tmp = row['DH_NEXT']
        print(index)
        if recv_s <= 500:
            dh_unit = (dh_max_next_tmp - dh_max_tmp) / (recv_s_max_next_tmp - recv_s_max_tmp) * 10
        else:
            dh_unit = dh_unit_1
        if index == 0:
            if recv_s <= recv_s_max_tmp:
                dh_tmp = dh_max_tmp - (recv_s_max_tmp - recv_s) * dh_unit / 10
                break
            elif recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                dh_tmp = dh_max_tmp + (recv_s - recv_s_max_tmp) * dh_unit
                break
            else:
                continue
        else:
            if recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                dh_tmp = dh_max_tmp + (recv_s - recv_s_max_tmp) * dh_unit / 10
                break
            else:
                continue
    for index, row in df3.iterrows():
        recv_s_max_tmp = row['RECV_S_MAX']
        whisk_time_max_tmp = row['WHISK_TIME']
        recv_s_max_next_tmp = row['RECV_S_MAX_NEXT']
        whisk_time_max_next_tmp = row['WHISK_TIME_NEXT']
        print(index)
        if recv_s <= 500:
            whisk_time_unit = (whisk_time_max_next_tmp - whisk_time_max_tmp) / (
                        recv_s_max_next_tmp - recv_s_max_tmp) * 10
        else:
            whisk_time_unit = whisk_time_unit_1
        if index == 0:
            if recv_s <= recv_s_max_tmp:
                whisk_time_tmp = whisk_time_max_tmp - (recv_s_max_tmp - recv_s) * whisk_time_unit / 10
                break
            elif recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                whisk_time_tmp = whisk_time_max_tmp + (recv_s - recv_s_max_tmp) * whisk_time_unit
                break
            else:
                continue
        else:
            if recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                whisk_time_tmp = whisk_time_max_tmp + (recv_s - recv_s_max_tmp) * whisk_time_unit / 10
                break
            else:
                continue
    return dh_tmp, whisk_time_tmp
recv_s_before = 30
recv_s_after = 50
recv_s_delta = recv_s_after - recv_s_before
dh_tmp, whisk_time_tmp = cal_dh_whisk_time(recv_s_before * 10)
dh_tmp_ad, whisk_time_tmp_ad = cal_dh_whisk_time(recv_s_after * 10)
iron_wt = df1['IRON_WT_NET'].values[0] / 10
dh_tmp = 8.03
dh_tmp_ad = 11.41
whisk_time_tmp = 11.7
whisk_time_tmp_ad = 12.95
whisk_time_tmp_delta = whisk_time_tmp_ad - whisk_time_tmp
iron_wt = 270
cao_wt = dh_tmp * iron_wt / 1000
cao_wt_ad = dh_tmp_ad * iron_wt / 1000
cao_wt_delta = cao_wt_ad - cao_wt
xlsx_name = 'D:/repos/sicost/二炼钢成本参数.xlsx'
df3 = pd.read_excel(xlsx_name)
coef1 = df3['炉均扒渣量'].values[0]
coef2 = df3['高炉渣'].values[0]
coef3 = df3['扒渣量增加'].values[0]
coef4 = df3['铁水包成本'].values[0]
coef5 = df3['搅拌桨成本'].values[0]
coef6 = df3['热轧品种的边际贡献'].values[0]
# 扒渣带铁量(t/包)
coef7 = coef1 - coef2 - cao_wt
# 30S扒渣带铁比例(%)
coef8 = coef7 / coef1
# 50S扒渣带铁比例(%)
coef9 = coef8 + 0.1
# 耐材增加/S
coef10 = whisk_time_tmp_delta / whisk_time_tmp / recv_s_delta
# 扒渣带铁
daitie = coef1 * coef8 / iron_wt
daitie_ad = (coef1 + cao_wt_delta) * (1 + coef3 * recv_s_delta) * coef9 / iron_wt
temp_down = 35
temp_down_ad = whisk_time_tmp_ad / whisk_time_tmp * temp_down
#耐材成本
naicai_cost = coef4 + coef5
naicai_cost_ad = naicai_cost * recv_s_delta * coef10
#带铁成本
iron_price = 3000
daitie_cost = daitie * iron_price
daitie_cost_ad = daitie_ad * iron_price
# 温降成本
temp_down_price = 1
temp_down_cost = temp_down * temp_down_price
temp_down_cost_ad = temp_down_ad * temp_down_price
#脱硫剂成本
cao_price = 0.91
cao_cost = dh_tmp * cao_price
cao_cost_ad = dh_tmp_ad * cao_price

total_cost = naicai_cost + daitie_cost + temp_down_cost + cao_cost
totol_cost_ad = naicai_cost_ad + daitie_cost_ad + temp_down_cost_ad + cao_cost_ad

print('finish')







